# -*- coding: utf-8 -*-

__author__ = 'gongqf'

import sqlite3
import re
from datetime import datetime
from ctms import app,  sa
from contextlib import closing
from flask import request, render_template, jsonify, redirect, url_for, Response, send_file, g, session
from config import DATABASE
from models import User


def init_db():
    with closing(connect_db()) as db:
        with app.open_resource('schema.sql') as f:
            db.cursor().executescript(f.read())
        db.commit()


def query_db(query, args=(), one=False):
    # print query
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value) for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv


def close_db():
    if hasattr(g, 'db'):
        g.db.close()


def connect_db():
    return sqlite3.connect(DATABASE)


def request_wants_json():
    best = request.accept_mimetypes.best_match(['application/json', 'text/html'])
    return best == 'application/json' and request.accept_mimetypes[best] > request.accept_mimetypes['text/html']


def del_none_value(d):
    for k in d.keys():
        if d[k] is None:
            d.pop(k)
    return d


def db_insert(tbname, kv):

    def exe_sql(d):
        d = del_none_value(d)
        cols = ','.join(d.keys())
        vals = ','.join(['\'%s\'' % v.replace("'", "''") for v in d.values()])
        sql = 'insert into %s (%s) values(%s)' % (tbname, cols, vals)
        # print sql
        query_db(sql)

    if type(kv) is list:
        for x in kv:
            if type(x) is dict:
                exe_sql(x)
    elif type(kv) is dict:
        exe_sql(kv)


def db_update(tbname, kv, cd):

    def exe_sql(d):
        d = del_none_value(d)
        cols = ['%s=' % k for k in d.keys()]
        vals = ['\'%s\'' % v for v in d.values()]

        cv = []
        for x in xrange(len(d)):
            cv.append('%s%s' % (cols[x], vals[x]))

        setval = ','.join(cv)
        query_db('update %s set %s where %s =\'%s\'' % (tbname, setval, cd[0], cd[1]))

    if type(kv) is list:
        for x in kv:
            if type(x) is dict:
                exe_sql(x)
    elif type(kv) is dict:
        exe_sql(kv)


def sarow_to_dict(rows):
    if type(rows) is tuple or type(rows) is list:
        l = []
        for row in rows:
            d = {}
            for column in row.__table__.columns:
                d[column.name] = getattr(row, column.name)
            l.append(d)
        return l
    else:
        return rows


def to_dict(items, use_none=True, del_key=None):
    '''
    items:  是把request.form获得的immutablemultidict对象用iteritems取得
    use_none:是否把空值('')转换为None返回,默认转换为None
    del_key: 是否删除指定的key,比如多余获取的input值,传入值可以是tuple或list,默认不删除
    '''
    d = {}
    for k, v in items:
        if v:
            d[k] = v
        elif use_none:
            d[k] = None
    if del_key:
        if type(del_key) is tuple or type(del_key) is list:
            for key in del_key:
                d.pop(key, None)
        else:
            d.pop(del_key, None)
    return d


def clean_html(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    # html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return re.sub(pattern, ' ', html).replace('> <', '><')


def clean_row(row):
    for k in row.keys():
        if row[k] is '':
            row[k] = None
    return row


@app.before_request
def before_request():
    g.db = connect_db()
    g.db.execute("PRAGMA foreign_keys=on")


@app.teardown_request
def teardown_request(exption):
    close_db()



@app.errorhandler(404)
def internal_error(error):
    return render_template('404.html'), 404


@app.errorhandler(500)
def internal_error(error):
    sa.session.rollback()
    return render_template('500.html'), 500


@app.route('/')
def index():
    # session.clear()
    return render_template('index.html')


@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('index'))


@app.route('/login', methods=['POST'])
def login():
    if request.method == 'POST':
        uuser = request.form['uuser']
        upass = request.form['upass']
        sql = 'select * from t_user where u_user=? and u_pass=?'
        print sql
        login_user = query_db(sql, (uuser, upass), one=True)
        if login_user:
            session['uid'] = login_user['u_id']
            session['name'] = login_user['u_name']
            session['user'] = login_user['u_user']
            return jsonify({"bSuccess": True, "iErr": 0})
        else:
            sql = 'select * from t_user where u_user=?'
            error_user = query_db(sql, (uuser,), one=True)
            if error_user:
                return jsonify({"bSuccess": False, "iErr": 300, "sMsg": u"密码错误"})
            else:
                return jsonify({"bSuccess": False, "iErr": 400, "sMsg": u"未找到用户"})


@app.route('/changepassword', methods=['POST'])
def changepassword():
    if request.method == 'POST':
        uid = session['uid']
        uopass = request.form['uopass']
        unpass = request.form['unpass']
        uqpass = request.form['uqpass']

        if unpass != uqpass:
            return jsonify({"bSuccess": False, "iErr": 301, "sMsg": u"密码不匹配"})

        sql = 'select * from t_user where u_id=?'
        exits_user = query_db(sql, (uid,), one=True)
        if exits_user:
            sql = 'select * from t_user where u_id=? and u_pass=?'
            ok_user = query_db(sql, (uid, uopass), one=True)
            if ok_user:
                sql = 'update t_user set u_pass=? where u_id=?'
                query_db(sql, (unpass, uid))
                g.db.commit()
                return jsonify({"bSuccess": True, "iErr": 0, "sMsg": u"密码修改成功"})
            else:
                return jsonify({"bSuccess": False, "iErr": 302, "sMsg": u"原密码错误"})
        else:
            return jsonify({"bSuccess": False, "iErr": 400, "sMsg": u"未找到用户"})


@app.route('/sysUser', methods=['GET', 'POST'])
def sysUser():
    sql='select * from t_user'
    users=query_db(sql)
    return render_template('sysUer.html', users=users)
    # return render_template('sysUser-i.html', users=users)


def execute_sql(tbl, cols='*', w=(), page=1, rows=10, sort=None, order=None):
    # sql_table = tbl
    # where_list = []
    where_expr = ''
    if type(cols) is list or type(cols) is tuple:
        cols = ','.join(cols)

    if w:
        where_expr = 'where %s' % ' and '.join(w)
    raw_sql = 'select %s from %s %s' % (cols, tbl, where_expr)
    print 'raw_sql: %s' % raw_sql
    count_sql = 'select count(*) as total from (%s)' % raw_sql
    print 'count_sql: %s' % count_sql

    if sort and order:
        rows_sql = '%s order by %s %s' % (raw_sql, sort, order)
    if page and rows:
        offset = (page-1)*rows
        rows_sql = '%s limit %d offset %d' % (rows_sql, rows, offset)
    print 'rows_sql: %s' % rows_sql
    rows = query_db(rows_sql)
    total = query_db(count_sql, one=True)['total']
    return total, rows


def safe_request(s, d=''):
    return request.args.get(s, d).replace("'", "''")

@app.route('/sysUser.json', methods=['GET', 'POST'])
def sysUser_json():
    # if request.method == 'GET':
    page = request.args.get('page', 1, type=int)
    rows = request.args.get('rows', 10, type=int)
    sort = safe_request('sort', 'u_id')
    order = safe_request('order', 'desc')
    u_id = request.args.get('u_id', 0, type=int)
    u_user = safe_request('u_user', '')
    u_name = safe_request('u_name', '')
    w = []
    w.append('u_id=%s' % u_id) if u_id else None
    w.append('u_user=\'%s\'' % u_user) if u_user else None
    w.append('u_name=\'%s\'' % u_name) if u_name else None

    total, rows = execute_sql(tbl='t_user', w=tuple(w), page=page, rows=rows, sort=sort, order=order)
    return jsonify(total=total, rows=rows)
    # return render_template('sysUser-i.html', users=users)